using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class Product : IProduct
    {
        // Methods
        public void Delete(int pro_id)
        {
            string cmdText = "UPDATE Product_Info SET IsDelete='1' WHERE ProductID = " + pro_id;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public ProductInfo getProductInfo(int pro_id)
        {
            string cmdText = "SELECT ProductID,TemplateId,MenuId,Title,Model,Pic1,Pic2,Pic3,Pic4,SellPoint,TrainingContent,TrainingUrl,Doc,WebSite,FunctionDescription,Specs,Particular,Fittings,Ads,AdType,Compete,IsFirst,KeyWord,Status,IsHot,Meta,Sorted,CreateTime,IsNew,Link_Url FROM Product_Info where ProductID=" + pro_id + " and IsDelete='0'";
            ProductInfo info = null;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    info = new ProductInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetString(13), reader.GetString(14), reader.GetString(15), reader.GetString(0x10), reader.GetString(0x11), reader.GetString(0x12), reader.GetByte(0x13), reader.GetString(20), reader.GetByte(0x15), reader.GetString(0x16), reader.GetByte(0x17), 0, reader.GetByte(0x18), reader.GetString(0x19), reader.GetInt32(0x1a), reader.GetString(0x1d));
                    info.CreateTime = reader.GetDateTime(0x1b).ToShortDateString();
                    info.IsNew = reader.GetByte(0x1c);
                    info.LinkUrl = reader.GetString(0x1d);
                }
            }
            return info;
        }

        public IList getProductList(int menu_id)
        {
            string cmdText = "SELECT ProductID,TemplateId,MenuId,Title,Model,Pic1,Pic2,Pic3,Pic4,SellPoint,TrainingContent,TrainingUrl,Doc,WebSite,FunctionDescription,Specs,Particular,Fittings,Ads,AdType,Compete,IsFirst,KeyWord,Status,IsHot,Meta,Sorted,CreateTime,IsNew,Link_Url FROM Product_Info where MenuId=" + menu_id + " and IsDelete='0' order by ProductID desc";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    ProductInfo info = new ProductInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetString(13), reader.GetString(14), reader.GetString(15), reader.GetString(0x10), reader.GetString(0x11), reader.GetString(0x12), reader.GetByte(0x13), reader.GetString(20), reader.GetByte(0x15), reader.GetString(0x16), reader.GetByte(0x17), 0, reader.GetByte(0x18), reader.GetString(0x19), reader.GetInt32(0x1a), reader.GetString(0x1d));
                    info.CreateTime = reader.GetDateTime(0x1b).ToShortDateString();
                    info.IsNew = reader.GetByte(0x1c);
                    list.Add(info);
                }
            }
            return list;
        }

        public void Insert(ProductInfo proinfo)
        {
            string cmdText = string.Concat(new object[] { 
            "insert into Product_Info(TemplateId,MenuId,Title,Model,Pic1,Pic2,Pic3,Pic4,SellPoint,TrainingContent,TrainingUrl,Doc,WebSite,FunctionDescription,Specs,Particular,Fittings,Ads,AdType,Compete,IsFirst,KeyWord,Status,IsDelete,IsHot,CreateTime,Meta,Sorted,IsNew,Link_Url ) values(", proinfo.TemplateId, ",", proinfo.MenuId, ",'", StringUtil.ReplaceStr(proinfo.Title), "','", StringUtil.ReplaceStr(proinfo.Model), "','", StringUtil.ReplaceStr(proinfo.Pic1), "','", StringUtil.ReplaceStr(proinfo.Pic2), "','", StringUtil.ReplaceStr(proinfo.Pic3), "','", StringUtil.ReplaceStr(proinfo.Pic4), 
            "','", StringUtil.ReplaceStr(proinfo.SellPoint), "','", StringUtil.ReplaceStr(proinfo.TrainingContent), "','", StringUtil.ReplaceStr(proinfo.TrainingUrl), "','", StringUtil.ReplaceStr(proinfo.Doc), "','", StringUtil.ReplaceStr(proinfo.WebSite), "','", StringUtil.ReplaceStr(proinfo.FunctionDescription), "','", StringUtil.ReplaceStr(proinfo.Specs), "','", StringUtil.ReplaceStr(proinfo.Particular), 
            "','", StringUtil.ReplaceStr(proinfo.Fittings), "','", StringUtil.ReplaceStr(proinfo.Ads), "',", proinfo.AdType, ",'", StringUtil.ReplaceStr(proinfo.Compete), "',", proinfo.IsFirst, ",'", StringUtil.ReplaceStr(proinfo.KeyWord), "',", proinfo.Status, ",0,", proinfo.IsHot, 
            ",getdate(),'", StringUtil.ReplaceStr(proinfo.Meta), "',", proinfo.Sorted, ",", proinfo.IsNew, ",'", proinfo.LinkUrl, "' ) "
         });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void Update(ProductInfo proinfo)
        {
            if (proinfo.Title == null)
            {
                proinfo.Title = string.Empty;
            }
            if (proinfo.Model == null)
            {
                proinfo.Model = string.Empty;
            }
            if (proinfo.Pic1 == null)
            {
                proinfo.Pic1 = string.Empty;
            }
            if (proinfo.Pic2 == null)
            {
                proinfo.Pic2 = string.Empty;
            }
            if (proinfo.Pic3 == null)
            {
                proinfo.Pic3 = string.Empty;
            }
            if (proinfo.Pic4 == null)
            {
                proinfo.Pic4 = string.Empty;
            }
            if (proinfo.SellPoint == null)
            {
                proinfo.SellPoint = string.Empty;
            }
            if (proinfo.TrainingContent == null)
            {
                proinfo.TrainingContent = string.Empty;
            }
            if (proinfo.TrainingUrl == null)
            {
                proinfo.TrainingUrl = string.Empty;
            }
            if (proinfo.Doc == null)
            {
                proinfo.Doc = string.Empty;
            }
            if (proinfo.WebSite == null)
            {
                proinfo.WebSite = string.Empty;
            }
            if (proinfo.FunctionDescription == null)
            {
                proinfo.FunctionDescription = string.Empty;
            }
            if (proinfo.Specs == null)
            {
                proinfo.FunctionDescription = string.Empty;
            }
            if (proinfo.Particular == null)
            {
                proinfo.Particular = string.Empty;
            }
            if (proinfo.Specs == null)
            {
                proinfo.Specs = string.Empty;
            }
            if (proinfo.Fittings == null)
            {
                proinfo.Fittings = string.Empty;
            }
            if (proinfo.Ads == null)
            {
                proinfo.Ads = string.Empty;
            }
            if (proinfo.Compete == null)
            {
                proinfo.Compete = string.Empty;
            }
            if (proinfo.KeyWord == null)
            {
                proinfo.KeyWord = string.Empty;
            }
            if (proinfo.Meta == null)
            {
                proinfo.Meta = string.Empty;
            }
            string cmdText = string.Concat(new object[] { 
            "UPDATE Product_Info SET TemplateId = ", proinfo.TemplateId, ",MenuId = ", proinfo.MenuId, ",Title = '", StringUtil.ReplaceStr(proinfo.Title), "',Model = '", StringUtil.ReplaceStr(proinfo.Model), "',Pic1 ='", StringUtil.ReplaceStr(proinfo.Pic1), "',Pic2 ='", StringUtil.ReplaceStr(proinfo.Pic2), "',Pic3 ='", StringUtil.ReplaceStr(proinfo.Pic3), "',Pic4 ='", StringUtil.ReplaceStr(proinfo.Pic4), 
            "',SellPoint ='", StringUtil.ReplaceStr(proinfo.SellPoint), "',TrainingContent ='", StringUtil.ReplaceStr(proinfo.TrainingContent), "',TrainingUrl ='", StringUtil.ReplaceStr(proinfo.TrainingUrl), "',Doc ='", StringUtil.ReplaceStr(proinfo.Doc), "',WebSite ='", StringUtil.ReplaceStr(proinfo.WebSite), "',FunctionDescription ='", StringUtil.ReplaceStr(proinfo.FunctionDescription), "',Specs ='", StringUtil.ReplaceStr(proinfo.Specs), "',Particular ='", StringUtil.ReplaceStr(proinfo.Particular), 
            "',Fittings ='", StringUtil.ReplaceStr(proinfo.Fittings), "',Ads ='", StringUtil.ReplaceStr(proinfo.Ads), "',AdType ='", proinfo.AdType, "',Compete ='", StringUtil.ReplaceStr(proinfo.Compete), "',IsFirst ='", proinfo.IsFirst, "',KeyWord='", StringUtil.ReplaceStr(proinfo.KeyWord), "',Status = '", proinfo.Status, "',IsHot ='", proinfo.IsHot, 
            "',Meta ='", StringUtil.ReplaceStr(proinfo.Meta), "',Sorted ='", proinfo.Sorted, "',IsNew ='", proinfo.IsNew, "',Link_Url ='", proinfo.LinkUrl, "' WHERE ProductID =", proinfo.Product_ID
         });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}